﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.IO;


namespace ESpace.DalTools.SqlServer
{
    public class SqlBuilder : SqlBuilderBase
    {
        #region 属性定义
        private string _TableName;
        private string _SavePath;
        private bool _IsDbGen;
        private bool _IsDropCode;
        private bool _IsEncrypt;
        private XmlDocument xmlDoc;
        private bool _IsHavePK = false;
        private bool _IsAllPK = false;
        private bool _IsHaveFK = false;
        private bool _IsNoLock = false;
        private string _NameSpace;
        private string _ClassName;

        public string NameSpace
        {
            get
            {
                return _NameSpace;
            }
            set
            {
                _NameSpace = value;
            }
        }

        public string ClassName
        {
            get
            {
                return _ClassName;
            }
            set
            {
                _ClassName = value;
                InitMetaData();
            }
        }
        /// <summary>
        /// 要生成存储过程的表名称，必须先设置此属性才能生成
        /// </summary>
        public string TableName
        {
            get
            {
                return _TableName;
            }
            set
            {
                _TableName = value;
                //InitMetaData();
            }
        }

        /// <summary>
        /// 生成的存储过程的保存路径
        /// </summary>
        public string SavePath
        {
            get
            {
                return _SavePath;
            }
            set
            {
                string sSplit = value.Substring(value.Length - 1);
                if (sSplit != @"\" && sSplit != "/")
                    _SavePath = value + @"\";
                else
                    _SavePath = value;
            }
        }

        /// <summary>
        /// 是否在数据库上直接生成存储过程
        /// </summary>
        public bool IsDbGen
        {
            get
            {
                return _IsDbGen;
            }
            set
            {
                _IsDbGen = value;
            }
        }

        /// <summary>
        /// 在代码中是否生成Drop语句
        /// </summary>
        public bool IsDropCode
        {
            get
            {
                return _IsDropCode;
            }
            set
            {
                _IsDropCode = value;
            }
        }

        /// <summary>
        /// 获取或设置存储过程是否加密
        /// </summary>
        public bool IsEncrypt
        {
            get
            {
                return _IsEncrypt;
            }
            set
            {
                _IsEncrypt = value;
            }
        }

        /// <summary>
        /// 获取表中是否有主键
        /// </summary>
        public bool IsHavePK
        {
            get
            {
                return _IsHavePK;
            }
        }

        /// <summary>
        /// 获取表中的列是否全部为主键
        /// </summary>
        public bool IsAllPK
        {
            get
            {
                return _IsAllPK;
            }
        }

        /// <summary>
        /// 获取表是否含有外键
        /// </summary>
        public bool IsHaveFK
        {
            get
            {
                return _IsHaveFK;
            }
        }

        #endregion

        /// <summary>
        /// 获取表格的所有列，在TableName字段变化时立即执行。
        /// </summary>
        private void InitMetaData()
        {
            DataTable dtColumns = GetColumns(_TableName);
            DataTable dtTable = GetATable(_TableName);
            DataSet dsMetaData = new DataSet("Schema");
            dsMetaData.Tables.Add(dtColumns);

            // 表中是否含有主键以及是否全部主键
            _IsHavePK = dtColumns.Select("IsPK='1'").Length != 0;
            _IsAllPK = dtColumns.Select("IsPK='0'").Length == 0;

            xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(dsMetaData.GetXml());


            //xmlElement.SetAttribute("StoreProcedureName", _StoreProcedureName);
            //xmlElement.SetAttribute("NameSpace", _NameSpace);
            //xmlElement.SetAttribute("IsInternal", _IsInternal.ToString());
            //xmlElement.SetAttribute("DBName", base.DBName);
            //xmlElement.SetAttribute("EncryptMethod", EncryptMethod.ToString());
            //xmlElement.SetAttribute("Key", base.Key);
            //xmlElement.SetAttribute("IV", base.IV);
            //xmlElement.SetAttribute("UserName", UserName);
            //xmlElement.SetAttribute("UserEMail", UserEMail);
            //xmlElement.SetAttribute("ModifyDate", DateTime.Now.ToString());



            XmlElement xmlElement = xmlDoc.DocumentElement;
            xmlElement.SetAttribute("ClassName", _ClassName);
            xmlElement.SetAttribute("NameSpace", _NameSpace);
            xmlElement.SetAttribute("TableName", _TableName);
            xmlElement.SetAttribute("IsDropCode", Convert.ToInt16(_IsDropCode).ToString());
            xmlElement.SetAttribute("IsEncrypt", Convert.ToInt16(_IsEncrypt).ToString());
            xmlElement.SetAttribute("IsNoLock", Convert.ToInt16(_IsNoLock).ToString());
            xmlElement.SetAttribute("DBName", base.DBName);
            xmlElement.SetAttribute("EncryptMethod", EncryptMethod.ToString());
            xmlElement.SetAttribute("Key", base.Key);
            xmlElement.SetAttribute("IV", base.IV);
            xmlElement.SetAttribute("UserName", UserName);
            xmlElement.SetAttribute("UserEMail", UserEMail);
            xmlElement.SetAttribute("ModifyDate", DateTime.Now.ToString());
            if (dtTable.Rows.Count != 0)
                xmlElement.SetAttribute("Owner", dtTable.Rows[0]["TableOwner"].ToString());
            //xmlDoc.ImportNode(GetForeignKeys(_TableName).FirstChild,true);
            _IsHaveFK = GetForeignKeys(_TableName).DocumentElement.ChildNodes.Count != 0;
            xmlElement.InnerXml += GetForeignKeys(_TableName).InnerXml;

#if DEBUG
            xmlDoc.Save(@"c:\SQLColumnsSchema.xml");
#endif
            //GetForeignKeys(_TableName).Save(@"c:\ForeignKey.xml");
        }

        /// <summary>
        /// 检查保存路径是否存在，如果不存在，则创建。
        /// </summary>
        private void CheckSavePath()
        {
            if (!Directory.Exists(_SavePath))
                Directory.CreateDirectory(_SavePath);
        }

        /// <summary>
        /// 生成Inser的存储过程
        /// </summary>
        public void BuilderInsertProc()
        {

            CheckSavePath();
            string sFilePath = _SavePath + _TableName + "Insert.cs";
            string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\Insert.xslt";
            TemplateTransform.TransformXslt(xmlDoc, sXsltPath, sFilePath);
        }

        /// <summary>
        /// 生成根据主键查询的存储过程
        /// </summary>
        public void BuilderUpdateProc()
        {
            if (_IsHavePK && !_IsAllPK)
            {
                CheckSavePath();
                string sFilePath = _SavePath + _TableName + "Update.cs";
                string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\Update.xslt";
                TemplateTransform.TransformXslt(xmlDoc, sXsltPath, sFilePath);
            }
        }

        /// <summary>
        /// 生成删除一条记录的存储过程
        /// </summary>
        public void BuilderDeleteProc()
        {
            if (_IsHavePK)
            {
                 CheckSavePath();
                 string sFilePath = _SavePath + _TableName + "Delete.cs";
                string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\Delete.xslt";
                TemplateTransform.TransformXslt(xmlDoc, sXsltPath, sFilePath);
            }
        }

        /// <summary>
        /// 生成获取所有记录的存储过程
        /// </summary>
        public void BuilderSelectAllProc()
        {
            CheckSavePath();
            string sFilePath = _SavePath + _TableName + "SelectAll.cs";
            string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\SelectAll.xslt";
            TemplateTransform.TransformXslt(xmlDoc, sXsltPath, sFilePath); ;
        }

        /// <summary>
        /// 生成获取所有记录的存储过程
        /// </summary>
        //public void BuilderSelectAllPagedProc()
        //{
        //    CheckSavePath();
        //    string sFilePath = _SavePath + _TableName + "SelectAllPaged.sql";
        //    string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\SelectAllPaged.xslt";
        //    TemplateTransform.TransformXslt(xmlDoc,sXsltPath,sFilePath);
        //    // 如果指示直接在数据库中生成存储过程，则执行文件
        //    if(_IsDbGen)
        //        ExecuteSqlFile(sFilePath);
        //}

        /// <summary>
        /// 生成根据外键查询的存储过程
        /// </summary>
        public void BuilderSelectByFKProc()
        {
            if (_IsHaveFK)
            {
                  CheckSavePath();
                  string sFilePath = _SavePath + _TableName + "SelectByFK.cs";
                string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\SelectByFK.xslt";
                TemplateTransform.TransformXslt(xmlDoc, sXsltPath, sFilePath);
            }
        }

        /// <summary>
        /// 生成根据主键获取记录的存储过程
        /// </summary>
        public void BuilderSelectByPKProc()
        {
            if (_IsHavePK)
            {
                CheckSavePath();
                string sFilePath = _SavePath + _TableName + "SelectByPK.cs";
                string sXsltPath = GetCurrentExecutePath() + @"Template\SqlServer\SQL\SelectByPK.xslt";
                TemplateTransform.TransformXslt(xmlDoc, sXsltPath, sFilePath);
            }
        }

        /// <summary>
        /// 执行.sql文件
        /// </summary>
        /// <param name="sFilePath">sql文件地址</param>
        private void ExecuteSqlFile(string sFilePath)
        {
            StreamReader sqlReader = null;
            try
            {
                SqlCommand comm = new SqlCommand();
                comm.Connection = _conn;
                sqlReader = new StreamReader(sFilePath, System.Text.Encoding.GetEncoding(936));
                string sSqls = sqlReader.ReadToEnd();

                // 分割sql语句,让它分批执行
                string sSplit = "\r\nGO";
                int iStartPosition = 0;
                int iEndPosition = sSqls.IndexOf(sSplit, iStartPosition);

                string sSql = null;
                while (iEndPosition >= 0)
                {
                    sSql = sSqls.Substring(iStartPosition, iEndPosition - iStartPosition);
                    iStartPosition = iEndPosition + sSplit.Length;
                    iEndPosition = sSqls.IndexOf(sSplit, iStartPosition);

                    comm.CommandText = sSql;
                    comm.ExecuteNonQuery();
                }
                if (iStartPosition != sSqls.Length)
                {
                    sSql = sSqls.Substring(iStartPosition, sSqls.Length - iStartPosition);
                    comm.CommandText = sSql;
                    comm.ExecuteNonQuery();
                }
            }
            finally
            {
                sqlReader.Close();
            }
        }
    }

}
